
package cassandrawindow;

/**
 * @author ADMIN
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.sql.Time;
import java.util.*;
import java.util.ArrayList;
/**
 *
 * @author ADMIN
 */
public class MysqlDataManagerMain {

    private Connection conn = null;
    private String TABLE_BANNERS                = "ox_banners";
    private String V3_DOMAIN_DATE_VMASK         = "v3_domain_date_vmask";
    private String V3_BAN_DATE_VMASK            = "v3_domain_date_vmask";
    private String V3_DOMAIN_DATE               = "v3_domain_date";//V3_DOMAIN_DATE
    
    public MysqlDataManagerMain() throws ClassNotFoundException, InstantiationException, IllegalAccessException
    {
        /*try
            {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(ACCOUNT.URL_MYSQL_MAIN , ACCOUNT.username_mysql_main, ACCOUNT.pass_mysql_main);//abc!@#
            //Debug.println("Connected to MySQL GFS Server database");
            }
            catch (Exception ex)
            {
            Debug.println("Couldn't connect to the MySQL database 5.9.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            Debug.println(ex.getMessage());
            Debug.println(ex.getMessage());
            }*/
        try {           
            Class.forName("com.mysql.jdbc.Driver").newInstance();            
            conn = DriverManager.getConnection(ACCOUNT.URL_MYSQL_MAIN_67 , ACCOUNT.username_mysql_main_67, ACCOUNT.pass_mysql_main_67);//abc!@#
            
            Debug.println("Connected to MySQL Server 5.9 database");
        } catch (SQLException ex) {
            ex.printStackTrace();
            Debug.println("Couldn't connect to the MySQL database 5.9.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            Debug.println(ex.getMessage());
            Debug.println(ex.getMessage());
        }

    }

    public void shutdownConnection()
    {
        try {
            conn.close();
            conn = null;  
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    public int getCountCampaign()
    {
        int retVal = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select COUNT(*) from "+ TABLE_BANNERS);
            while (rs.next())
            {
                retVal = rs.getInt(1);
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return retVal;
    }

    public List getCampaignIdBySlice(String date) throws InterruptedException
    {
        List infor = new ArrayList();
       /* int countAll = getCountCampaign();
        int div = (int) Math.floor(countAll/5000);
        
        for (int i = 0 ; i <= div ; i ++)
        {*/
            try
            {
                Statement st= (Statement)conn.createStatement();
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"' limit " + 5000*i + "," + 5000);
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"'ORDER BY campaignid DESC");

                /*String sql = "SELECT DISTINCT C.campaignid  FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` = C.`campaignid`  "+
                    "LEFT JOIN `selfserving_inform` B ON (C.`campaignid`=B.`id` AND B.date='"+ date +"')"+
                    "LEFT JOIN `selfserving_inform` D ON (C.`bannerid` =D.`id` AND D.date='"+ date +"') "+
                    "WHERE A.`revenue_type` IN(5,10) AND (A.`isExpired` IN (0,3,4,5,10,11) OR ( B.`date`='"+ date +"' ))"+
                    "AND (C.`status` IN (1,8) OR (D.`date` = '"+ date +"' )) AND (C.`deleted`=0 OR (C.`deleted`=1 AND D.`date` ='"+ date +"' ))";
                */
                 String sql =  "SELECT DISTINCT A.campaignid FROM `ox_campaigns` A "+                                
                                //"INNER JOIN ox_banners B "+
                                //"ON B.campaignid = A.campaignid "+
                                "WHERE A.`revenue_type` IN (5,6,8,10) ";
                                //"AND '"+date+"' BETWEEN B.`activate` AND B.`expire` "+
                                //"GROUP BY A.`uid`";
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    infor.add(rs.getString("A.campaignid"));
                }
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return infor;
    }

    public List getCampaignIdBySliceCPM(String date) throws InterruptedException
    {
        List infor = new ArrayList();
       /* int countAll = getCountCampaign();
        int div = (int) Math.floor(countAll/5000);

        for (int i = 0 ; i <= div ; i ++)
        {*/
            try
            {
                Statement st= (Statement)conn.createStatement();
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"' limit " + 5000*i + "," + 5000);
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"'ORDER BY campaignid DESC");

                /*String sql = "SELECT DISTINCT C.campaignid  FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` = C.`campaignid`  "+
                    "LEFT JOIN `selfserving_inform` B ON (C.`campaignid`=B.`id` AND B.date='"+ date +"')"+
                    "LEFT JOIN `selfserving_inform` D ON (C.`bannerid` =D.`id` AND D.date='"+ date +"') "+
                    "WHERE A.`revenue_type` IN(5,10) AND (A.`isExpired` IN (0,3,4,5,10,11) OR ( B.`date`='"+ date +"' ))"+
                    "AND (C.`status` IN (1,8) OR (D.`date` = '"+ date +"' )) AND (C.`deleted`=0 OR (C.`deleted`=1 AND D.`date` ='"+ date +"' ))";
                */
                 String sql =  "SELECT DISTINCT A.campaignid FROM `ox_campaigns` A "+
                                //"INNER JOIN ox_banners B "+
                                //"ON B.campaignid = A.campaignid "+
                                "WHERE A.`revenue_type` IN (13) ";
                                //"AND '"+date+"' BETWEEN B.`activate` AND B.`expire` "+
                                //"GROUP BY A.`uid`";
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    infor.add(rs.getString("A.campaignid"));
                }
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return infor;
    }

    public List getCampaignIdBySliceMobile(String date) throws InterruptedException
    {
        List infor = new ArrayList();
       /* int countAll = getCountCampaign();
        int div = (int) Math.floor(countAll/5000);

        for (int i = 0 ; i <= div ; i ++)
        {*/
            try
            {
                Statement st= (Statement)conn.createStatement();
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"' limit " + 5000*i + "," + 5000);
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"'ORDER BY campaignid DESC");

                String sql = "SELECT DISTINCT A.`campaignid` FROM `ox_banners` A"+
                   "INNER JOIN `ox_campaigns` B ON A.`campaignid`=B.`campaignid"+
                    "WHERE B.`revenue_type`=11 AND A.`status`= B.`status`= A.`deleted`=0";
                //Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    infor.add(rs.getString("A.campaignid"));
                }
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return infor;
    }

    public List getBannerIDByCampaignId(int campaignId,String date)
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
            String sql = "SELECT DISTINCT C.bannerid FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` =C.`campaignid`  "+                   
                    "WHERE C.`campaignid`= "+ campaignId +" AND A.`revenue_type` IN(13)";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("C.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return infor;
    }

    public List getBannerIDByCPM()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
            String sql = "SELECT DISTINCT C.bannerid FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` =C.`campaignid`  "+
                    "WHERE A.`revenue_type` IN(13)";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("C.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return infor;
    }

    public List getBannerIDByCPCandCPCPlus()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
            /*String sql ="SELECT B.bannerid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) AND A.`isExpired` NOT IN (1, 2, 3) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` AND B.`status` = 1 AND B.`deleted` = 0 "
                    + "ORDER BY B.bannerid DESC";*/
                                
            String sql ="SELECT B.bannerid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(B.`expire`,INTERVAL 1 DAY) AND B.`status` IN (1,5) AND B.`deleted` = 0 "
                    //+ "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` "
                    + "ORDER BY B.bannerid DESC";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("B.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return infor;
    }
    
    public List getBannerIDByMobile()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay                                            
            String sql ="SELECT B.bannerid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 11) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(B.`expire`,INTERVAL 1 DAY) AND B.`status` IN (1,5) AND B.`deleted` = 0 "
                    //+ "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` "
                    + "ORDER BY B.bannerid DESC";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("B.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return infor;
    }
    

    public Map getBannerIDandCapmIDByCPCandCPCPlus()
    {
        Map<String, String> banAndCamp = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus            
           /* String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) AND A.`isExpired` NOT IN (1, 2, 3) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` AND B.`status` = 1 AND B.`deleted` = 0 "
                    + "ORDER BY B.bannerid DESC";*/
            String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5 ) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) "
                    + "ORDER BY B.bannerid DESC";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                banAndCamp.put(rs.getString("B.bannerid"),rs.getString("A.campaignid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return banAndCamp;
    }

    public Map getBannerIDandCapmIDByMobile()
    {
        Map<String, String> banAndCamp = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus            
           /* String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) AND A.`isExpired` NOT IN (1, 2, 3) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` AND B.`status` = 1 AND B.`deleted` = 0 "
                    + "ORDER BY B.bannerid DESC";*/
            String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 11) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) "
                    + "ORDER BY B.bannerid DESC";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                banAndCamp.put(rs.getString("B.bannerid"),rs.getString("A.campaignid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return banAndCamp;
    }
    
    
    public List getCampIDByMobile()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay            
            String sql ="SELECT A.campaignid "
                    + "FROM `ox_campaigns` A "                   
                    + "WHERE A.`revenue_type` = 11 "
                    + "AND CURRENT_DATE BETWEEN A.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) ";
            
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("A.campaignid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return infor;
    }
    
    public Map getBannerIDandCapmIDByCPCandCPCPlusExpire15()
    {
        Map<String, String> banAndCamp = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus            
            
            String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5 ) "
                    + "AND A.`expire` ='2013-03-15' "
                    + "ORDER BY B.bannerid DESC";
            
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                banAndCamp.put(rs.getString("A.campaignid"),rs.getString("B.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return banAndCamp;
    }
    
    
    public Map getBannerIDandCapmIDByCPCandCPCPlusSoloEnbac()
    {
        Map<String, String> banAndCamp = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();          
            String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid "
                    + "WHERE (A.campaignid = '1021159' OR A.campaignid = '1021653') "                    
                    + "ORDER BY B.bannerid DESC";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                banAndCamp.put(rs.getString("B.bannerid"),rs.getString("A.campaignid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return banAndCamp;
    }
    
    public List getBannerIDByCampaignIdCPM(int campaignId,String date)
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
            String sql = "SELECT DISTINCT C.bannerid FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` =C.`campaignid`  "+
                    "LEFT JOIN `selfserving_inform` B ON (C.`campaignid`=B.`id` AND B.date='"+ date +"')"+
                    "LEFT JOIN `selfserving_inform` D ON (C.`bannerid` =D.`id` AND D.date='"+ date +"') "+
                    "WHERE C.`campaignid`= "+ campaignId +" AND A.`revenue_type` IN(13) AND (A.`isExpired` IN (0,3,4,5,10,11) OR ( B.`date`='"+ date +"' ))"+
                    "AND (C.`status` IN (1,8) OR (D.`date` = '"+ date +"' )) AND (C.`deleted`=0 OR (C.`deleted`=1 AND D.`date` ='"+ date +"' ))";
            //Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("C.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return infor;
    }

    public Map getMaxValueDaybyBannerId(String date)
    {
        Map<Integer, Long> MaxValue = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "SELECT DISTINCT C.bannerid, A.maxValueDay FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` =C.`campaignid`  "+
                    "LEFT JOIN `selfserving_inform` B ON (C.`campaignid`=B.`id` AND B.date='"+ date +"')"+
                    "LEFT JOIN `selfserving_inform` D ON (C.`bannerid` =D.`id` AND D.date='"+ date +"') "+
                    "WHERE A.`revenue_type` IN(5,10) AND (A.`isExpired` IN (0,3,4,5,10,11) OR ( B.`date`='"+ date +"' ))"+
                    "AND (C.`status` IN (1,8) OR (D.`date` = '"+ date +"' )) AND (C.`deleted`=0 OR (C.`deleted`=1 AND D.`date` ='"+ date +"' )) GROUP BY  C.bannerid,'"+ date +"'";
            //Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                MaxValue.put(rs.getInt("C.bannerid"), rs.getLong("A.maxValueDay"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return MaxValue;
    }

    public Map getMaxValueDaybyBannerIdCPC(String date)
    {
        Map<Integer, Long> MaxValue = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "SELECT DISTINCT A.campaignid, A.maxValueDay FROM `ox_campaigns` A "+
                    //"INNER JOIN `ox_banners` C ON A.`campaignid` =C.`campaignid`  "+
                    "WHERE A.`revenue_type` IN(5,10,0)";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                MaxValue.put(rs.getInt("A.campaignid"), rs.getLong("A.maxValueDay"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return MaxValue;
    }

    public Map getMaxValueDaybyBannerIdMobile(String date)
    {
        Map<Integer, Long> MaxValue = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "SELECT DISTINCT A.campaignid, A.maxValueDay FROM `ox_campaigns` A "+                    
                    "WHERE A.`revenue_type` IN(11)";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                MaxValue.put(rs.getInt("A.campaignid"), rs.getLong("A.maxValueDay"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return MaxValue;
    }
    
    public List getBannerIDByCampaignIdMobile(int campaignId,String date)
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "SELECT DISTINCT A.`bannerid` FROM `ox_banners` A"+
                   "INNER JOIN `ox_campaigns` B ON A.`campaignid`=B.`campaignid"+
                    "WHERE A.`campaignid`= "+ campaignId +" AND B.`revenue_type`=11 AND A.`status`= B.`status`= A.`deleted`=0";           
            
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("C.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return infor;

    }

    public Map getAllBannerHasDomain(String date)
    {
        Map<String,String> mp=new HashMap<String, String>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            //ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = '"+ date +"' ORDER BY bannerid DESC");
            ResultSet rs = st.executeQuery("SELECT B.bannerid,B.list_all_site FROM ox_campaigns A INNER JOIN ox_banners B ON A.campaignid=B.campaignid WHERE DATE(A.updated) = '"+ date +"' AND A.revenue_type IN(5,10)");

            while (rs.next())
            {
                mp.put(rs.getString("bannerid"), rs.getString("list_all_site"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return mp;
    }

    public List getValidBanner(String date)
    {
        List addInfo = new ArrayList();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT B.bannerid FROM ox_campaigns A INNER JOIN ox_banners B ON A.campaignid=B.campaignid WHERE DATE(A.updated) = '"+ date +"' AND A.revenue_type IN(5,10)");

            while (rs.next())
            {
                addInfo.add(rs.getString("bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return addInfo;
    }
    
    public Map getPriceByDomain()
    {        
        Map<String, Integer> PriceByDomain = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name, price FROM selfserving_price A WHERE A.`type`=1");

            while (rs.next())
            {
                PriceByDomain.put(rs.getString("name"), rs.getInt("price"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return PriceByDomain;
    }

    public Map getPriceByDomainCPC()
    {
        Map<String, Integer> PriceByDomain = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name,price FROM `selfserving_price` A WHERE A.`type` IN(1)");

            while (rs.next())
            {
                PriceByDomain.put(rs.getString("name"), rs.getInt("price"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return PriceByDomain;
    }

    //get price with cpcplus
    public Map getPriceByDomainCPCPlus()
    {
        Map<String, Integer> PriceByDomain = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name, price FROM selfserving_price A WHERE A.`type` IN(1,5)");

            while (rs.next())
            {
                PriceByDomain.put(rs.getString("name"), rs.getInt("price"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return PriceByDomain;
    }

    
    
    public List getAllBanneridStoporPause(String dt)
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT distinct id  FROM `selfserving_inform` A WHERE DATE = '"+ dt +"' and type in (4,16,17,19)");

            while (rs.next())
            {
                infor.add(rs.getInt("id"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        
        return infor;
    }
    
    public Map getAllUserActive()
    {
        Map<Integer, String> allUsers = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            //ResultSet rs = st.executeQuery("SELECT user_id,balance,promotions,groupid FROM selfserving_users WHERE active = '1'");
            ResultSet rs = st.executeQuery("SELECT user_id,balance,promotions,groupid FROM selfserving_users");
            while (rs.next())
            {
                //if((rs.getInt("balance") > 0) ||(rs.getInt("promotions") > 0))
                //{
                    allUsers.put(rs.getInt("user_id"),rs.getString("balance")+"_"+rs.getString("promotions")+"_"+rs.getString("groupid"));
                //}
            }            
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return allUsers;
    }

    public Map getAllUserActiveCpcPlus()
    {
        Map<Integer, String> allUsers = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT user_id,balance,promotions FROM cpcplus_users_balances");

            while (rs.next())
            {
                //if((rs.getInt("balance") > 0) ||(rs.getInt("promotions") > 0))
                //{
                    allUsers.put(rs.getInt("user_id"),rs.getString("balance")+"_"+rs.getString("promotions"));
                //}
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return allUsers;
    }


    public Map getAllGroupID()
    {
        Map<Integer, Integer> allGroupId = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT groupid,typegroup FROM selfserving_users_group");

            while (rs.next())
            {               
                    allGroupId.put(rs.getInt("groupid"),rs.getInt("typegroup"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return allGroupId;
    }
    
    public Map getCampaignAndUserid(String date) throws InterruptedException
    {        
            Map<Integer, String> CampaignAndUserid = new HashMap<>();
            
            try
            {
                Statement st= (Statement)conn.createStatement();
                /*
                String sql = "SELECT uid,GROUP_CONCAT(cmp) FROM ( SELECT DISTINCT C.campaignid AS cmp, A.uid AS uid "+
                            "FROM `ox_campaigns` A " +
                            "INNER JOIN `ox_banners` C " +
                            "ON A.`campaignid` = C.`campaignid` "+
                            "LEFT JOIN `selfserving_inform` B "+
                            "ON (C.`campaignid` = B.`id` AND B.date = '"+date+"') "+
                            "LEFT JOIN `selfserving_inform` D "+
                            "ON (C.`bannerid` = D.`id` AND D.date = '"+date+"') "+
                            "WHERE   A.`revenue_type` IN (5, 10) "+
                            "AND (   A.`isExpired` IN (0, 3, 4, 5, 10, 11) "+
                            "OR (B.`date` = '"+date+"')) "+
                            "AND (C.`status` IN (1, 8) OR (D.`date` = '"+date+"')) "+
                            "AND (C.`deleted` = 0 OR (C.`deleted` = 1 AND D.`date` = '"+date+"'))) AS su "+
                            "GROUP BY uid";*/
                  String sql = "SELECT A.`uid` AS userid, GROUP_CONCAT(A.`campaignid`) "+
                                "FROM `ox_campaigns` A "+
                                //"INNER JOIN ox_banners B "+
                                //"ON B.campaignid = A.campaignid "+
                                "WHERE A.`revenue_type` IN (5) "+
                                //"AND '"+date+"' BETWEEN B.`activate` AND B.`expire` "+
                                "GROUP BY A.`uid`";
                Debug.println(sql);

                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignAndUserid.put(rs.getInt("userid"),rs.getString("GROUP_CONCAT(A.`campaignid`)"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return CampaignAndUserid;
    }

    //lay nhung campaign thuoc cpc thuong
    public List getCampaignCPC() throws InterruptedException
    {        
            List CampaignCPC = new ArrayList();
            
            try
            {
                Statement st= (Statement)conn.createStatement();              
                  String sql = "SELECT campaignid "+
                                "FROM `ox_campaigns` "+                               
                                "WHERE revenue_type IN (5)";                                                                
                Debug.println(sql);

                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPC.add(rs.getLong("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return CampaignCPC;
    }

    //lay nhung campaign thuoc cpc thuong
    public List getCampaignCPCandCPCPLUS() throws InterruptedException
    {
            List CampaignCPC = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT "+
                        "DISTINCT (A.`campaignid`) "+
                        "FROM "+
                        "`ox_campaigns` A " +
                        "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) "+
                        //"AND A.`isExpired` NOT IN (1, 2, 3) "+
                        "AND CURRENT_DATE BETWEEN A.`activate`"+
                        "AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) "+
                        "AND A.`deleted` = 0 ";
                       
                /*String sql = "SELECT "+
                        "DISTINCT (A.`campaignid`) "+
                        "FROM "+
                        "`ox_campaigns` A " +
                        "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) "+
                        "AND A.`isExpired` NOT IN (1, 2, 3) "+
                        "AND CURRENT_DATE BETWEEN A.`activate`"+
                        "AND A.`expire` "+
                        "AND A.`status` = 1 "+
                        "AND A.`deleted` = 0 ";*/
                
                /* String sql = "SELECT "+
                        "DISTINCT (A.`campaignid`) "+
                        "FROM "+
                        "`ox_campaigns` A " +
                        "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) "+
                        "AND A.`isExpired` NOT IN (1, 2, 3) "+
                        "AND A.`deleted` = 0 ";*/
                Debug.println(sql);

                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPC.add(rs.getString("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return CampaignCPC;
    }

     //lay nhung campaign thuoc cpc plus
    public List getCampaignCPCPlus() throws InterruptedException
    {
            List CampaignCPCPlus = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT campaignid "+
                                "FROM `ox_campaigns` "+
                                "WHERE revenue_type IN (10)";
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPCPlus.add(rs.getLong("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return CampaignCPCPlus;
    }

    public List getCampaignCPM() throws InterruptedException
    {
            List CampaignCPCPlus = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT campaignid "+
                                "FROM `ox_campaigns` "+
                                "WHERE revenue_type IN (13)";
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPCPlus.add(rs.getLong("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return CampaignCPCPlus;
    }

    public List getCampaignCPMV2() throws InterruptedException
    {
            List CampaignCPCPlus = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT campaignid "+
                                "FROM `ox_campaigns` "+
                                "WHERE revenue_type IN (13)";
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPCPlus.add(rs.getString("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return CampaignCPCPlus;
    }
    

    public List getValidDomain()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT DISTINCT name FROM selfserving_site_channel WHERE parentid = 0");

            while (rs.next())
            {
                infor.add(rs.getString("name"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return infor;
    }

    public void insertDataDomainData(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_DOMAIN_DATE_VMASK +"(campaignid, bannerid, domain, totalclick, totalview,realclick, realview,clickcharge,viewcharge, domaincrc, partnerid, istarget, domaingroup, domaingroupcrc, dt, preoder, money) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

    }

    public void insertSliceDataCampaignAndBanner(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_DATE_VMASK +"(campaignid,bannerid,totalclick,totalview,realclick,realview,clickcharge,viewcharge,uc,uv,MB,MT,MN,Other,dt,money) values " + value;
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
    }

    public Map getUvUcByCampaignid(String date)
    {
        Map<Integer, String> uv_uc = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaignid,uc,uv FROM vmask_cpm_campaign_uv_uc WHERE dt = '"+date+"'");
            Debug.println("SELECT campaignid,uc,uv FROM vmask_cpm_campaign_uv_uc WHERE dt = '"+date+"'");
            while (rs.next())
            {
                uv_uc.put(rs.getInt("campaignid"), rs.getString("uc")+"_"+rs.getString("uv"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return uv_uc;
    }
    
    public Map getUvUcByCampaignidMobile(String date)
    {
        Map<Integer, String> uv_uc = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaignid,uc,uv FROM mobile_cpc_campaign_uv_uc WHERE dt = '"+date+"'");
            Debug.println("SELECT campaignid,uc,uv FROM vmask_cpm_campaign_uv_uc WHERE dt = '"+date+"'");
            while (rs.next())
            {
                uv_uc.put(rs.getInt("campaignid"), rs.getString("uc")+"_"+rs.getString("uv"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return uv_uc;
    }
    

    public Map getUvUcByBannerid(String date)
    {
        Map<Integer, String> uv_uc = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,uc,uv FROM vmask_cpm_banner_uv_uc WHERE dt = '"+date+"'");
            Debug.println("SELECT bannerid,uc,uv FROM vmask_cpm_banner_uv_uc WHERE dt = '"+date+"'");
            while (rs.next())
            {
                uv_uc.put(rs.getInt("bannerid"), rs.getString("uc")+"_"+rs.getString("uv"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return uv_uc;
    }

    public Map getUvUcByBanneridMobile(String date)
    {
        Map<Integer, String> uv_uc = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,uc,uv FROM mobile_cpc_banner_uv_uc WHERE dt = '"+date+"'");
            Debug.println("SELECT bannerid,uc,uv FROM mobile_cpc_banner_uv_uc WHERE dt = '"+date+"'");
            while (rs.next())
            {
                uv_uc.put(rs.getInt("bannerid"), rs.getString("uc")+"_"+rs.getString("uv"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return uv_uc;
    }
    
    public Map getAllBannerRunning()
    {
        Map<String,String> mp=new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            //ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = '"+ date +"' ORDER BY bannerid DESC");
            ResultSet rs = st.executeQuery("SELECT B.`bannerid`,B.list_site "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) AND A.`isExpired` NOT IN (1, 2, 3) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` AND B.`status` = 1 AND B.`deleted` = 0 "
                    + "ORDER BY B.bannerid DESC");

            Debug.println("SELECT B.`bannerid`,B.list_site "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) AND A.`isExpired` NOT IN (1, 2, 3) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` AND B.`status` = 1 AND B.`deleted` = 0 "
                    + "ORDER BY B.bannerid DESC");
            
            while (rs.next())
            {
                mp.put(rs.getString("B.bannerid"), rs.getString("B.list_site"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return mp;
    }
    
    public Map getDataMobile()
    {
        Map<String,String> mp=new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
           
            String sql ="SELECT DISTINCT (A.`bannerid`),A.bannertype AS bannertype,A.is_smallbiz,C.price "
                    + "FROM `ox_banners` A  "
                    + "INNER JOIN `ox_campaigns` B ON A.`campaignid` = B.`campaignid` "
                    + "INNER JOIN  selfserving_price_mobile C ON (C.typead = A.is_smallbiz) "                    
                    + "WHERE B.`revenue_type`=11 AND CURRENT_DATE BETWEEN A.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) "                    
                    + "ORDER BY A.is_smallbiz ASC";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                mp.put(rs.getString("bannerid"), rs.getString("price")+"_"+rs.getString("bannertype")+"_"+rs.getString("is_smallbiz"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return mp;
    }
    
    public Map getTypeMobile()
    {
        Map<String,String> mp=new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
           
            String sql ="SELECT DISTINCT (A.`campaignid`),A.bannertype AS bannertype,A.is_smallbiz  "
                    + "FROM `ox_banners` A  "
                    + "INNER JOIN `ox_campaigns` B ON A.`campaignid`=B.`campaignid` "
                    + "INNER JOIN selfserving_price s ON s.is_smallbiz = A.is_smallbiz "                    
                    + "WHERE B.`revenue_type`=11 AND CURRENT_DATE BETWEEN A.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) AND A.`status` IN (1,5) AND A.`deleted` = 0 "                    
                    + "ORDER BY A.is_smallbiz ASC";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                mp.put(rs.getString("campaignid"), rs.getString("bannertype")+"_"+rs.getString("is_smallbiz"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return mp;
    }
    
    
    
}
